/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package sharefileserver;

import java.io.File;
import java.rmi.RemoteException;
import java.rmi.server.ServerNotActiveException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import sharefileinterface.FileShared;
import sharefileinterface.StatedFileShare;

/**
 *
 * @author anwedo
 */
public class RMIServer extends UnicastRemoteObject implements sharefileinterface.ShareFileInterface {

    private Connection conn = null;

    public RMIServer() throws RemoteException {

        super();

        File f = new File("ManageFile.db");
        if (!f.exists()) {
            try {
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:ManageFile.db");
                // create a tables     
                Statement s = conn.createStatement();
                String createTable = "CREATE TABLE SharedFiles (fid INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(100), owner varchar(16),type char(1), size int)";
                s.executeUpdate(createTable);
                // s = conn.createStatement();
                createTable = "CREATE TABLE Users (macadd varchar(20) primary key,computer_name varchar(100), ip varchar(16), status char(3), connectCount int)";
                s.executeUpdate(createTable);
                //  s = conn.createStatement();                                           
                createTable = "CREATE TABLE Downloaded (fid INTEGER,destination varchar(20), status char(3), hasPart int)";
                s.executeUpdate(createTable);
                // s = conn.createStatement();
                createTable = "CREATE TABLE SubFile (fid INTEGER, filepath varchar(200), size int)";
                s.executeUpdate(createTable);
                // close and cleanup
                //s.close();//do not close connection for member join and have new handle
                
                

                System.out.println("New file created!");
            } catch (SQLException | ClassNotFoundException ex) {
                Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            try {
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:ManageFile.db");
            } catch (ClassNotFoundException | SQLException ex) {
                Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    @Override
    public int registFile(String filename, String sender, long size) {
        System.out.println("File: " + filename + ", sender: " + sender + ",size: " + size);
        //connect to DB
        int fid = 0;
        try {
            System.out.println("Opened database successfully");
            Statement s = conn.createStatement();

            //add file to DB
            String addRow = "INSERT INTO SharedFiles(name, owner, type, size) VALUES ('"
                    + filename + "', '" + sender + "', 'f', "
                    + size + ")";
            s.executeUpdate(addRow);
            ResultSet rs = s.getGeneratedKeys();
            if (rs != null && rs.next()) {
                fid = rs.getInt(1);
            }

            // Fetch table to test
            String selTable = "SELECT * FROM SharedFiles";
            s.execute(selTable);
            rs = s.getResultSet();
            while ((rs != null) && (rs.next())) {
                System.out.println(rs.getString(1) + " : " + rs.getString(2) + " : " + rs.getString(3) + " : " + rs.getString(4) + " : " + rs.getInt(5));
            }
        } catch (SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
        return fid;
    }

    @Override
    public int checkJoin(String macAdd, String comName) {
        try {
            System.out.println("client : " + getClientHost());
            Statement s = conn.createStatement();
            String selTable = "SELECT * FROM Users WHERE macadd ='" + macAdd + "'";
            s.execute(selTable);
            ResultSet rs = s.getResultSet();
            //check existed and update infomation
            if (rs.next()) {
                String updateTable = "UPDATE Users SET ip='" + getClientHost() + "', computer_name='" + comName + "',status ='on', connectCount = 0 WHERE macadd ='" + macAdd + "'";
                s.executeUpdate(updateTable);
            } else {
                String updateTable = "INSERT INTO Users VALUES('" + macAdd + "', '" + comName + "', '" + getClientHost() + "', 'on', 0)";
                s.executeUpdate(updateTable);
            }
            //s.close();
        } catch (ServerNotActiveException | SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 1;
    }

    @Override
    public Vector<StatedFileShare> getFileList(String key) {
        Vector<StatedFileShare> vf = new Vector<>();
        try {
//            Connection conn = null;
//            try {
//                Class.forName("org.sqlite.JDBC");
//                conn = DriverManager.getConnection("jdbc:sqlite:ManageFile.db");
//            } catch (Exception e) {
//                System.err.println(e.getClass().getName() + ": " + e.getMessage());
//                System.exit(0);
//            }
            System.out.println("Opened database");
            Statement s = conn.createStatement();
            String selTable;
            if ("".equals(key)) {
                selTable = "SELECT s.fid,s.name,s.owner,s.type,s.size,u.computer_name FROM SharedFiles s, Users u "
                        + "WHERE u.status='on' AND u.macadd=s.owner UNION "
                        + "SELECT s.fid,s.name,s.owner,s.type,s.size,u.computer_name FROM SharedFiles s, Users u, Downloaded d "
                        + "WHERE u.status='on' AND d.status='ed' AND u.macadd=d.destination AND s.fid=d.fid";

            } else {
                selTable = "SELECT s.fid,s.name,s.owner,s.type,s.size,u.computer_name FROM SharedFiles s, Users u "
                        + "WHERE u.status='on' AND u.macadd=s.owner AND s.name LIKE '%" + key + "%'  UNION "
                        + "SELECT s.fid,s.name,s.owner,s.type,s.size,u.computer_name FROM SharedFiles s, Users u, Downloaded d "
                        + "WHERE u.status='on' AND d.status='ed' AND u.macadd=d.destination AND s.fid=d.fid AND s.name LIKE '%" + key + "%'";
            }
            s.execute(selTable);
            ResultSet rs = s.getResultSet();
            while ((rs != null) && (rs.next())) {
                System.out.println(rs.getString(1) + " : " + rs.getString(2) + " : " + rs.getInt(3));
                StatedFileShare sfs = new StatedFileShare();
//                sfs.setFid(rs.getInt("fid"));
//                sfs.setFilename(rs.getString("name"));
//                sfs.setSender(rs.getString("owner"));
//                sfs.setType(rs.getString("type"));
//                sfs.setSize(rs.getInt("size"));
//                sfs.setSenderName(rs.getString("u.computer_name"));
                sfs.setFid(rs.getInt(1));
                sfs.setFilename(rs.getString(2));
                sfs.setSender(rs.getString(3));
                sfs.setType(rs.getString(4));
                sfs.setSize(rs.getInt(5));
                sfs.setSenderName(rs.getString(6));
                vf.add(sfs);
            }
//            s.close();
//            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return vf;
    }

    @Override
    public int registDirectory(String dirname, String sender, long size, Vector<FileShared> fileList) throws RemoteException {
        try {
            System.out.println("Opened database successfully");
            Statement s = conn.createStatement();

            //add file to DB
            String addRow = "INSERT INTO SharedFiles(name, owner, type, size) VALUES ('"
                    + dirname + "', '" + sender + "', 'd', "
                    + size + ")";
            s.executeUpdate(addRow);
            // Fetch table to test
            String selTable = "SELECT * FROM SharedFiles";
            s.execute(selTable);
            ResultSet rs = s.getResultSet();
            while ((rs != null) && (rs.next())) {
                System.out.println(rs.getString(1) + " : " + rs.getString(2) + " : " + rs.getString(3) + " : " + rs.getString(4) + " : " + rs.getInt(5));
            }
            //add sub file into folder up to database
            selTable = "SELECT fid FROM SharedFiles WHERE name='" + dirname + "' AND owner='" + sender + "'";
            s.execute(selTable);
            rs = s.getResultSet();
            int fid = 0;
            if (rs.next()) {
                fid = rs.getInt(1);
            }
            for (FileShared fileShared : fileList) {
                //add file to DB
                addRow = "INSERT INTO SubFile(fid, filepath, size) VALUES ('"
                        + fid + "', '" + fileShared.getFilename() + "', "
                        + fileShared.getSize() + ")";
                s.executeUpdate(addRow);
            }
        } catch (SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
        return 1;
    }
    
    @Override
    public Vector<String> folderContent(int folderID) throws RemoteException {
        Vector<String> rfc = new Vector<>();
        try {
            Statement s = conn.createStatement();
            String selTable = "SELECT filepath FROM SubFile WHERE fid = " + folderID;
            s.execute(selTable);
            ResultSet rs = s.getResultSet();
            while ((rs != null) && (rs.next())) {
                rfc.add(rs.getString(1));
            }
        } catch (SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rfc;
    }

    @Override
    public Vector<String> getIPListByFid(int fid) throws RemoteException {
        Vector<String> rlip = new Vector<>();
        try {
            Statement s = conn.createStatement();
            String selTable = "SELECT ip FROM Users u, SharedFiles s,Downloaded d WHERE "
                    + "u.macadd = d.destination AND s.fid=d.fid AND  u.status='on' AND s.fid=" + fid
                    + " UNION SELECT ip FROM Users u, SharedFiles s WHERE u.status='on' AND s.fid=" + fid;
            s.execute(selTable);
            ResultSet rs = s.getResultSet();
            String ip;
            while ((rs != null) && (rs.next())) {
                ip = rs.getString(1);
                rlip.add(ip);
            }
        } catch (SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rlip;
    }

    @Override
    public int getFileSize(int fid, int k) throws RemoteException {
        int size = 0;
        try {
            Statement s = conn.createStatement();
            String selTable = "SELECT size FROM SubFile WHERE fid  = " + fid;
            s.execute(selTable);
            ResultSet rs = s.getResultSet();
            if (rs != null) {
                for (int i = 0; i < k; i++) {
                    rs.next();
                }
                size = rs.getInt(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return size;
    }

    @Override
    public int changeStatusWithMac(String macAdd) throws RemoteException {
        try {
            System.out.println("client : " + getClientHost());
            Statement s = conn.createStatement();
            String selTable = "SELECT * FROM Users WHERE macadd ='" + macAdd + "'";
            s.execute(selTable);
            ResultSet rs = s.getResultSet();
            //check existed and update infomation
            if (rs != null) {
                rs.next();
                String updateTable = "UPDATE Users SET status ='off' WHERE macadd ='" + macAdd + "'";
                s.executeUpdate(updateTable);
            }
            //s.close();
        } catch (ServerNotActiveException | SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 1;
    }

    @Override
    public int changeStatusForIP(String ip) throws RemoteException {
        try {
            System.out.println("client : " + getClientHost());
            Statement s = conn.createStatement();
            String selTable = "SELECT macadd FROM Users WHERE ip ='" + ip + "'";
            s.execute(selTable);
            ResultSet rs = s.getResultSet();
            //check existed and update infomation
            if (rs != null) {
                rs.next();
                String updateTable = "UPDATE Users SET status ='off' WHERE ip ='" + rs.getString(1) + "'";
                s.executeUpdate(updateTable);
            }
            //s.close();
        } catch (ServerNotActiveException | SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 1;
    }

        //Cap nhat trang thai
    @Override
    public String updateStatus(String sender, String status) throws RemoteException {
        try {
            System.out.println("Opened database successfully");
            Statement s = conn.createStatement();
            String sql;
            if (status.equalsIgnoreCase("on")) {
                sql = "UPDATE from Users "
                        + "set status = 'on'";
                s.execute(sql);
            }

            if (status.equalsIgnoreCase("off")) {
                sql = "UPDATE from Users "
                        + "set status = 'off'";
                s.execute(sql);
            }

            if (status.equalsIgnoreCase("addConn")) {
                sql = "select * from Users where "
                        + " macadd = '" + sender + "'";
                s.execute(sql);
                ResultSet rs2 = s.getResultSet();
                int connectCount = 0;
                if (rs2 != null && rs2.next()) {
                    connectCount = rs2.getInt("connectCount");
                    connectCount++;
                }
                //step 2: update
                sql = "UPDATE Users set "
                        + "connectCount = " + connectCount + " where "
                        + " macadd = '" + sender + "'";
                s.executeUpdate(sql);
            }
        } catch (SQLException ex) {
            return "";
        }
        return status;
    }

    @Override
    public String hasFilePart(String sender, int fid, int part) throws RemoteException {
        //connect to DB
        try {
            Statement s = conn.createStatement();
            //check
            String sql = "select * From Downloaded where "
                    + " fid = " + fid
                    + " and destination = '" + sender + "'";
            s.execute(sql);
            ResultSet rs = s.getResultSet();
            String addRow, updateRow;
            if (rs == null || !rs.next()) {
                //insert
                addRow = "INSERT INTO Downloaded(fid, destination, status, hasPart) VALUES ('"
                        + fid + "', '" + sender + "', 'ok', "
                        + part + ")";
                s.executeUpdate(addRow);
                //update connectCount
                updateStatus(sender, "addConn");
            } else {
                //update
                updateRow = "UPDATE Downloaded set "
                        + " hasPart =" + part + " where "
                        + " fid = " + fid
                        + " and destination = '" + sender + "'";
                s.executeUpdate(updateRow);
            }
        } catch (SQLException ex) {
            return "";
        }
        return new Integer(part).toString();
    }

    @Override
    public LinkedHashMap<String, Integer> needFilePart(String sender, int fid) throws RemoteException {
        LinkedHashMap<String, Integer> clietList = new LinkedHashMap<>();
        try {
            Statement s = conn.createStatement();
            //check
            String sql = "select Downloaded.* From Downloaded, Users where "
                    + " fid = " + fid
                    + " and connectCount < 10 "
                    + " order by Users.connectCount, hasPart DESC";
            s.execute(sql);
            ResultSet rs = s.getResultSet();
            while ((rs != null) && (rs.next())) {
                clietList.put(rs.getString("destination"), rs.getInt("hasPart"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return clietList;
    }

}
